﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Aspose.Cells;
using System.IO;
using System.Net;

namespace Common
{
    public class ExcelHelper
    {

        /// <summary>
        /// 导入Excel
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="columsStr">列名数组</param>
        /// <returns></returns>
        public static bool ImportExcel(DataTable dt, string[] columsStr)
        {

            try
            {
                //创建一个工作簿
                Workbook wb = new Workbook();

                if (dt.Rows.Count <= 0)
                    return false;

                Worksheet fisrtSheet = wb.Worksheets[0];
                fisrtSheet.Name = "员工名册";

                //为单元格添加样式
                Aspose.Cells.Style style = wb.CreateStyle();

                style.HorizontalAlignment = TextAlignmentType.Center; //文字居中

                style.Font.Size = 12; //字体大小

                style.Font.IsBold = true;  //粗体

                style.Borders[Aspose.Cells.BorderType.LeftBorder].LineStyle = Aspose.Cells.CellBorderType.Double; //应用边界线 左边界线
                style.Borders[Aspose.Cells.BorderType.RightBorder].LineStyle = Aspose.Cells.CellBorderType.Double; //应用边界线 右边界线
                style.Borders[Aspose.Cells.BorderType.TopBorder].LineStyle = Aspose.Cells.CellBorderType.Double; //应用边界线 上边界线
                style.Borders[Aspose.Cells.BorderType.BottomBorder].LineStyle = Aspose.Cells.CellBorderType.Double; //应用边界线 下边界线 



                for (int i = 0; i < columsStr.Length; i++)
                {
                    fisrtSheet.Cells[0, i].PutValue(columsStr[i]);

                    fisrtSheet.Cells[0, i].SetStyle(style);
                }

                //循环绑定数据
                try
                {

                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        fisrtSheet.Cells[i + 1, 0].PutValue(dt.Rows[i]["Id"].ToString());
                        fisrtSheet.Cells[i + 1, 1].PutValue(dt.Rows[i]["Account"].ToString());
                        fisrtSheet.Cells[i + 1, 2].PutValue(dt.Rows[i]["RealName"].ToString());
                        fisrtSheet.Cells[i + 1, 3].PutValue(dt.Rows[i]["Sex"].ToString());
                        fisrtSheet.Cells[i + 1, 4].PutValue(dt.Rows[i]["IDCard"].ToString());
                        fisrtSheet.Cells[i + 1, 5].PutValue(dt.Rows[i]["Address1"].ToString());
                        fisrtSheet.Cells[i + 1, 6].PutValue(dt.Rows[i]["Address2"].ToString());
                        fisrtSheet.Cells[i + 1, 7].PutValue(dt.Rows[i]["Brithday"].ToString());
                        fisrtSheet.Cells[i + 1, 8].PutValue(dt.Rows[i]["PositionName"].ToString());
                        fisrtSheet.Cells[i + 1, 9].PutValue(dt.Rows[i]["DepartmentName"].ToString());


                    }
                }
                catch (Exception ex)
                {

                    throw;
                }



                //保存Excel
                wb.Save(System.Web.HttpContext.Current.Server.MapPath("/员工名册.xlsx"), SaveFormat.Xlsx);

                // HttpDownload(System.Web.HttpContext.Current.Server.MapPath("/员工名册.xlsx"), "C:\\Users\\Administrator\\Desktop");



                return true;
            }
            catch (Exception ex)
            {
                return false;
            }

        }


        /// <summary>
        /// 导出Excel
        /// <param name="fileUrl">excel物理路径</param>
        /// <returns></returns>
        public static DataTable ExportExcel(string fileUrl)
        {
            try
            {
                //加载指定 的工作簿
                Workbook wb = new Workbook(fileUrl);

                //找到该工作簿中的第一个工作表加载其所有的单元格
                Cells allCells = wb.Worksheets[0].Cells;

                //从第一个单元格的坐标开始读取到最后一个单元格坐标
                DataTable dt = allCells.ExportDataTableAsString(0, 0, allCells.MaxRow + 1, allCells.MaxColumn + 1);

                return dt;

            }
            catch (Exception ex)
            {
                return new DataTable();
            }

        }

        /// <summary>
        /// http下载文件
        /// </summary>
        /// <param name="url">下载文件地址</param>
        /// <param name="path">文件存放地址，包含文件名</param>
        /// <returns></returns>
        public static bool HttpDownload(string url, string path)
        {
            string tempPath = System.IO.Path.GetDirectoryName(path) + @"\temp";
            System.IO.Directory.CreateDirectory(tempPath);  //创建临时文件目录
            string tempFile = tempPath + @"\" + System.IO.Path.GetFileName(path) + ".temp"; //临时文件
            if (System.IO.File.Exists(tempFile))
            {
                System.IO.File.Delete(tempFile);    //存在则删除
            }
            try
            {
                FileStream fs = new FileStream(tempFile, FileMode.Append, FileAccess.Write, FileShare.ReadWrite);
                // 设置参数
                HttpWebRequest request = WebRequest.Create(url) as HttpWebRequest;
                //发送请求并获取相应回应数据
                HttpWebResponse response = request.GetResponse() as HttpWebResponse;
                //直到request.GetResponse()程序才开始向目标网页发送Post请求
                Stream responseStream = response.GetResponseStream();
                //创建本地文件写入流
                //Stream stream = new FileStream(tempFile, FileMode.Create);
                byte[] bArr = new byte[1024];
                int size = responseStream.Read(bArr, 0, (int)bArr.Length);
                while (size > 0)
                {
                    //stream.Write(bArr, 0, size);
                    fs.Write(bArr, 0, size);
                    size = responseStream.Read(bArr, 0, (int)bArr.Length);
                }
                //stream.Close();
                fs.Close();
                responseStream.Close();
                System.IO.File.Move(tempFile, path);
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
